package com.softer.chengxin.train.dao;

import com.softer.chengxin.common.domin.Page;
import com.softer.chengxin.train.dao.provider.TrainDaoProvider;
import com.softer.chengxin.train.entity.Train;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author Gong Zhiyang
 * @date 2018/10/13
 */
@Mapper
@Repository
public interface TrainDao {
    /**
     * 校验培训人员是否已经添加过该证书
     *
     * @param certificate 证书编号
     * @return 证书编号
     */
    @Select("SELECT certificate FROM company_train WHERE  certificate=#{certificate} and state=2")
    String checkPersonTrain(@Param("certificate") String certificate);

    /**
     * 项目经理培训时，校验是否人员和编码是否对应
     *
     * @param name 培训人员
     * @param code 证书编码
     * @return
     */
    @Select("SELECT name FROM project_manager_train WHERE `name`=#{name} AND `code`=#{code}")
    String checkProjectManagerTrain(@Param("name") String name, @Param("code") String code);

    /**
     * 添加培训记录
     *
     * @param train 培训记录
     * @return 添加结果
     */
    @Insert("INSERT INTO company_train(gmt_create,gmt_modified,person_id,subject,train_begin_time,train_end_time,corp_id,state,department,certificate,file_id,subject_type) " +
            " VALUES(NOW(),NOW(),#{train.personId},#{train.subject},#{train.trainBeginTime},#{train.trainEndTime},#{train.corpId},0,#{train.department},#{train.certificate},#{train.fileId},#{train.subjectType}) ")
    int add(@Param("train") Train train);

    /**
     * 提交培训记录
     *
     * @param id 培训ID
     * @return 结果
     */
    @Update("UPDATE company_train SET state=1 WHERE id=#{id}")
    int submit(@Param("id") Integer id);

    /**
     * 编辑培训记录
     *
     * @param train 培训记录
     * @return 结果
     */
    @Update("UPDATE company_train SET gmt_modified=now(), person_id=#{train.personId},train_begin_time=#{train.trainBeginTime},train_end_time=#{train.trainEndTime},`subject`=#{train.subject}," +
            "department=#{train.department},state=0,certificate=#{train.certificate},file_id=#{train.fileId},subject_type=#{train.subjectType} WHERE id=#{train.id}")
    int edit(@Param("train") Train train);

    /**
     * 刪除培训记录
     *
     * @param id 培训ID
     * @return 结果
     */
    @Delete("DELETE FROM company_train WHERE id=#{id}")
    int delete(@Param("id") Integer id);

    /**
     * 查询培训列表数量
     *
     * @param name  名字
     * @param train 查询实体
     * @return 数量
     */
    @SelectProvider(type = TrainDaoProvider.class, method = "queryTrainNum")
    int queryTrainNum(@Param("name") String name, @Param("train") Train train);

    /**
     * 查询培训列表
     *
     * @param page  页面实体
     * @param name  名字
     * @param train 查询实体
     * @return 列表
     */
    @SelectProvider(type = TrainDaoProvider.class, method = "queryTrainList")
    List<Train> queryTrainList(@Param("page") Page page, @Param("name") String name, @Param("train") Train train);

    /**
     * 查询具体人员培训数量
     *
     * @param id 培训ID
     * @return 列表
     */
    @SelectProvider(type = TrainDaoProvider.class, method = "queryPersonTrainNum")
    int queryPersonTrainNum(@Param("id") Integer id);

    /**
     * 查询具体人员培训列表
     *
     * @param page 页面实体
     * @param id   培训ID
     * @return 列表
     */
    @SelectProvider(type = TrainDaoProvider.class, method = "queryPersonTrainList")
    List<Train> queryPersonTrainList(@Param("page") Page page, @Param("id") Integer id);

    /**
     * 查询培训详情
     *
     * @param id 培训id
     * @return 详情
     */
    @Select("SELECT a.id,a.person_id as personId,b.`name`,c.name as qyName,b.duties AS dutiesCode,(SELECT name FROM dic_info WHERE code = b.duties) AS duties,a.`subject`,a.train_begin_time as trainBeginTime,a.department as departmentCode,(SELECT name FROM dic_info WHERE code = a.department) AS department,a.train_end_time as trainEndTime,d.project_name as projectName,a.opinion," +
            "a.subject_type as subjectType,a.certificate,a.file_id as fileId FROM company_train a LEFT JOIN company_person b ON a.person_id=b.id LEFT JOIN company_base_info c on c.id = a.corp_id LEFT JOIN company_project d ON b.project_id=d.id where a.id=#{id}")
    Train detail(@Param("id") Integer id);

    /**
     * 通过人员ID查询项目名称
     *
     * @param id 人员ID
     * @return 项目名称
     */
    @Select("SELECT b.project_name FROM company_person a LEFT JOIN company_project b ON a.project_id =b.id WHERE a.id=#{id}")
    String getProjectName(@Param("id") String id);

    /**
     * 通过人员ID查询人员名字
     *
     * @param id 人员ID
     * @return 人员名字
     */
    @Select("SELECT `name` FROM company_person where id=#{id}")
    String getPersonName(@Param("id") Integer id);
}
